# 该功能是用来修改加工服务器上的sql数据中的revisdsta字段

import pymysql
import re

# 这是采集服务器的mysql数据库的连接属性
CJMYSQL_CONNECT = {
    'MYSQL_HOST': '114.115.219.219',
    'MYSQL_PORT': 3306,
    'MYSQL_DB': 'globallegaldatabase',
    'MYSQL_USER': 'root',
    'MYSQL_PASSWD': 'xinanzhengfadaqxueapp03!',
    'MYSQL_CHARSET': 'utf8'
}

# 这是加工服务器的mysql数据库连接属性
BSMYSQL_CONNEXT = {
    'MYSQL_HOST': '114.116.40.73',
    'MYSQL_PORT': 3306,
    'MYSQL_DB': 'southwestpoliticsun',
    'MYSQL_USER': 'lj',
    'MYSQL_PASSWD': 'xinanzhengfadaqxuelj!',
    'MYSQL_CHARSET': 'utf8'
}

# 连接采集数据库
cjhost = CJMYSQL_CONNECT['MYSQL_HOST']
cjport = CJMYSQL_CONNECT['MYSQL_PORT']
cjdb = CJMYSQL_CONNECT['MYSQL_DB']
cjuser = CJMYSQL_CONNECT['MYSQL_USER']
cjpassword = CJMYSQL_CONNECT['MYSQL_PASSWD']
CJMYSQL_TABLE = ['lawtext']  # 这是采集数据库存储数据的表
cjurlconn = pymysql.connect(host=cjhost, port=cjport, db=cjdb, user=cjuser, passwd=cjpassword, charset='utf8')
# 连接部署服务器数据库
bshost = BSMYSQL_CONNEXT['MYSQL_HOST']
bsport = BSMYSQL_CONNEXT['MYSQL_PORT']
bsdb = BSMYSQL_CONNEXT['MYSQL_DB']
bsuser = BSMYSQL_CONNEXT['MYSQL_USER']
bspassword = BSMYSQL_CONNEXT['MYSQL_PASSWD']
BSMYSQL_TABLE = {'lawtext': 'law', 'lawcasetext': 'case'}  # 这是采集数据库存储数据的表
bsurlconn = pymysql.connect(host=bshost, port=bsport, db=bsdb, user=bsuser, passwd=bspassword, charset='utf8')
SQL_COUNTRYS = ['LAWCOUNTRYMLXY', 'LAWCOUNTRYTG', 'LAWCOUNTRYYN', 'LAWCOUNTRYYDNXY', 'LAWCOUNTRYLW', 'LAWCOUNTRYMD']

for cjtable in CJMYSQL_TABLE:
    bstable = BSMYSQL_TABLE[cjtable]
    for country in SQL_COUNTRYS:
        # 创建游标
        print("当前处理的是:" + country)
        i = 0
        cjurlcursor = cjurlconn.cursor()

        cjsql = "SELECT ID,LegalName,Organizaation,PublishDate,EffectiveDate,SortA,SortB,SortC,SortD,SORTE,SORTF," \
                "Keyword,SORTG,ChapNo,Articles,Chapter,Section,SYS_FLD_MARKSTATE,SYS_FLD_DIGITFILENAME," \
                "FileUrl,DownLoadUrl,DownLoadWebNameC, DownLoadWebNameE, " \
                "SYSID,Revisionmark from %s WHERE SortA = '%s' AND Website = 'International Labour Organization'" % (cjtable, country)

        try:
            cjurlcursor.execute(cjsql)
            rs = cjurlcursor.fetchall()
            bsurlcursor = bsurlconn.cursor()
            for r in rs:
                SortA = r[5]
                SYSID = r[23]
                bscsql = "SELECT * FROM `%s` WHERE SortA = '%s' AND SYSID = '%s'" % (bstable, SortA, SYSID)
                try:
                    bsurlcursor.execute(bscsql)
                    bsrs = bsurlcursor.fetchall()
                    if bsrs:
                        bsr = list(r)
                        for j in range(len(bsr)):
                            if bsr[j] is None:
                                bsr[j] = ''
                        title = re.sub(r'''"''', r"'", bsr[1])
                        bsr[1] = title
                        if bstable == "law":
                            bssql = '''UPDATE %s set ReviseDateStr = "%s" WHERE SortA = "%s" AND SYSID = "%s"''' % (bstable, bsr[24], SortA, SYSID)

                        try:
                            bsurlcursor.execute(bssql)
                            bsurlconn.commit()
                        except Exception as e:
                            print("插入ID=" + str(r[0]) + "数据失败:" + str(e) + '\n')
                    else:
                        print("该条数据在加工服务器上已存在:" + str(r[0]) + ' ' + SortA + ' ' + SYSID)
                    i = i + 1
                    print("\r已处理: %d" % i, end="")
                except Exception as e:
                    print("117服务器查询出错:" + str(e))
            bsurlcursor.close()
        except Exception as e:
            print("采集服务器查询出错:" + str(e))
        cjurlcursor.close()
bsurlconn.close()
cjurlconn.close()
